﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_review_patch_levels]
#-- Purpose:		Compares the current patch levels of servers to the current
#--					service pack / cumulative update patch levels
#--	Last Update:	05/21/2013
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_review_patch_levels]
AS

SET NOCOUNT ON

SELECT		ssd_sqlserver_name_ro,
			(CASE	WHEN t2.sql_product_version IS NULL THEN 'Unknown'
					WHEN t2.sql_product_version = t3.ver_current_version THEN 'Yes'
					WHEN t2.sql_product_version BETWEEN t3.ver_current_sp_version AND t3.ver_current_version THEN 'Partial' 
					ELSE 'No' END) is_patch_current,
			t2.sql_product_version,
			t3.ver_current_sp_version,
			t3.ver_current_version
FROM		[dbo].[tblMSX_server_discovery] t1
JOIN		[dbo].[tblMSX_server_sql_info] t2 ON ssd_id = t2.sql_ssd_id
LEFT JOIN	[dbo].[tblMSX_sql_version] t3 ON t2.sql_version_major_ro = t3.ver_major_ro
			AND t2.sql_version_minor_ro = t3.ver_minor_ro
WHERE		ssd_is_disabled = 0
ORDER BY	(CASE	WHEN t2.sql_product_version IS NULL THEN 3
					WHEN t2.sql_product_version = t3.ver_current_version THEN 0
					WHEN t2.sql_product_version BETWEEN t3.ver_current_sp_version AND t3.ver_current_version THEN 1
					ELSE 2 END),
			t2.sql_version_major_ro DESC,
			t2.sql_version_minor_ro DESC,
			t2.sql_product_version DESC

SET NOCOUNT OFF
